<?php

	class Swamivisits_proxy extends Model
	{
		public function __construct()
		{
			parent::__construct();
			
			$this->load->model('values/swamivisits/Swamivisits_registration_value');
		}
						
		public function getEventName( $eventID )
		{
			
			$tmp = $this->db->query('
								SELECT			se.description
								FROM			sv_event se
								WHERE			se.sv_event_id = ' . $eventID  . '
							 ');
							 
			if( $tmp->num_rows() > 0)
				return $tmp->row()->description;
			else
				return "";
		}

		public function getEventRegistrationFee( $eventID )
		{
			
			$tmp = $this->db->query('
								SELECT			se.registration_fee
								FROM			sv_event se
								WHERE			se.sv_event_id = ' . $eventID  . '
								AND				se.enabled = 1
							 ');
							 
			if( $tmp->num_rows() > 0)
				return $tmp->row()->registration_fee;
			else
				return 0;
		}

		//Get total number of people  registered for event
		public function getTotalNumReg( $eventID, $curyear )
		{
			
			$tmp = $this->db->query('
								SELECT			SUM(svrh.numreg) AS totnumreg
								FROM			sv_reg_history svrh
								WHERE			svrh.sv_event_id_fk = ' . $eventID  . '
								AND				svrh.enabled = 1
								AND				svrh.year = ' . $this->db->escape( $curyear ) . '								
							 ');
							 
			if( $tmp->num_rows() > 0)
				return $tmp->row()->totnumreg;
			else
				return 0;
		}

		//Check if date for event has passed
		public function isEventValid( $eventID )
		{
			
			$tmp = $this->db->query('
								SELECT			sve.sv_event_id
								FROM			sv_event sve
								WHERE			sve.enabled = 1
								AND				sve.sv_event_id = ' . $eventID  . '
								AND				NOW() < sve.end_date
							 ');
							 
			if( $tmp->num_rows() > 0)
				return TRUE;
			else
				return FALSE;
		}

		public function insertSVRegistrationPayPal( $sv_registration_value, $curyear, $pay_txn_id)
		{
			$sv_registration_value = Swamivisits_registration_value::cast($sv_registration_value);
			
			$this->db->query('
								INSERT INTO			sv_reg_history
													(
														sv_event_id_fk,
														year,
														first_name,
														last_name,
														email,
														numreg,
														reg_fee,
														payment_type,
														payment_transaction_id,
														enabled,
														date_modified,
														date_created,
														amount_paid,
														payment_status
													)
								VALUES				(
														' . $this->db->escape($sv_registration_value->getEventID())  . ',
														' . $this->db->escape($curyear)  . ',
														' . $this->db->escape($sv_registration_value->getFirstName())  . ',
														' . $this->db->escape($sv_registration_value->getLastName())  . ',
														' . $this->db->escape($sv_registration_value->getEmail())  . ',
														' . $this->db->escape($sv_registration_value->getNumRegistered())  . ',
														' . $this->db->escape($sv_registration_value->getRegistrationFee())  . ',
														' . $this->db->escape("paypal")  . ',
														' . $this->db->escape($pay_txn_id)  . ',
														1,
														Now(),
														Now(),
														' . $this->db->escape($sv_registration_value->getTotalRegistrationFee())  . ',
														1														
													)
							 ');
			
			$SVRegHistoryId = getMaxDbId( 'sv_reg_history_id', 'sv_reg_history' );
			
			return $SVRegHistoryId;
		}

		public function insertSVRegistrationCheck( $sv_registration_value, $curyear)
		{
			$sv_registration_value = Swamivisits_registration_value::cast($sv_registration_value);
			
			$this->db->query('
								INSERT INTO			sv_reg_history
													(
														sv_event_id_fk,
														year,
														first_name,
														last_name,
														email,
														numreg,
														reg_fee,
														payment_type,
														enabled,
														date_modified,
														date_created,
														amount_paid,
														payment_status
													)
								VALUES				(
														' . $this->db->escape($sv_registration_value->getEventID())  . ',
														' . $this->db->escape($curyear)  . ',
														' . $this->db->escape($sv_registration_value->getFirstName())  . ',
														' . $this->db->escape($sv_registration_value->getLastName())  . ',
														' . $this->db->escape($sv_registration_value->getEmail())  . ',
														' . $this->db->escape($sv_registration_value->getNumRegistered())  . ',
														' . $this->db->escape($sv_registration_value->getRegistrationFee())  . ',
														' . $this->db->escape("check")  . ',
														1,
														Now(),
														Now(),
														' . $this->db->escape($sv_registration_value->getTotalRegistrationFee())  . ',
														2														
													)
							 ');
			
			$SVRegHistoryId = getMaxDbId( 'sv_reg_history_id', 'sv_reg_history' );
			
			return $SVRegHistoryId;
		}

		//Check if person already registered for event 
		public function isEventRegistered( $eventID, $firstname, $lastname, $email_contact )
		{
			
			$tmp = $this->db->query('
								SELECT			sv.sv_reg_history_id
								FROM			sv_reg_history sv
								WHERE			sv.first_name = ' . $this->db->escape($firstname) . '
								AND				sv.last_name = ' . $this->db->escape($lastname) . '
								AND				sv.email = ' . $this->db->escape($email_contact) . '
								AND				sv.sv_event_id_fk = ' . $this->db->escape($eventID) . '
								AND				sv.enabled = 1
							 ');
							 
			if( $tmp->num_rows() > 0)
				return TRUE;
			else
				return FALSE;
		}

		public function getEventUnpaidCheckInfo( $curyear )
		{
			
			$unpaidchecklist = $this->db->query('
												SELECT    	sv.sv_reg_history_id,
															sv.first_name,
												          	sv.last_name,
															sv.email,
															sv.numreg,
															sv.amount_paid,
															sve.description
												FROM      	sv_reg_history sv
															INNER JOIN sv_event sve ON sve.sv_event_id=sv.sv_event_id_fk
												WHERE       sv.payment_status <> 1
												AND			sv.payment_type = ' . $this->db->escape( 'check' ) . '
												AND			sv.year = ' . $this->db->escape( $curyear ) . '
												AND			sv.enabled = 1
											  ');

			
			$regInfo = array();
			if( $unpaidchecklist->num_rows() > 0)
			{
				foreach ($unpaidchecklist->result() as $regIdRow)
				{
					$checkInfo = array();		
					array_push($checkInfo, $regIdRow->sv_reg_history_id);					
					array_push($checkInfo, $regIdRow->first_name);					
					array_push($checkInfo, $regIdRow->last_name);					
					array_push($checkInfo, $regIdRow->email);					
					array_push($checkInfo, $regIdRow->numreg);					
					array_push($checkInfo, $regIdRow->amount_paid);					
					array_push($checkInfo, $regIdRow->description);					
					
					array_push($regInfo, $checkInfo);					
				}
			}
				
			return $regInfo;
		}

		public function getEventUnpaidCheckRegId( $curyear )
		{
			
			$unpaidchecklist = $this->db->query('
												SELECT    	sv.sv_reg_history_id
												FROM      	sv_reg_history sv
												WHERE       sv.payment_status <> 1
												AND			sv.payment_type = ' . $this->db->escape( 'check' ) . '
												AND			sv.year = ' . $this->db->escape( $curyear ) . '
												AND			sv.enabled = 1
											  ');
			
			$regId = array();
			if( $unpaidchecklist->num_rows() > 0)
			{
				foreach ($unpaidchecklist->result() as $regIdRow)
				{
					array_push($regId, $regIdRow->sv_reg_history_id);					
				}
			}
				
			return $regId;
		}

		public function completeSVRegistrationCheck( $svRegHistoryId, $checknum )
		{
			
			$this->db->query('
								UPDATE			sv_reg_history
								SET				payment_status = 1,
												check_num = ' . $this->db->escape($checknum) . ',
												date_modified = Now()
								WHERE			sv_reg_history_id = ' . $svRegHistoryId  . '
								AND				payment_type = ' . $this->db->escape('check') . '
							 ');
							 
			return $svRegHistoryId;
		}

		//Get event value object
		public function getEventInfoYear( $svRegHistoryId, $curyear )
		{
			$this->load->model('values/swamivisits/Swamivisits_registration_value');			
			
			$eventlist = $this->db->query('
												SELECT    	sv.sv_reg_history_id,
															sv.first_name,
												          	sv.last_name,
															sv.email,
															sv.numreg,
															sv.payment_type,
															sv.check_num,
															sv.payment_transaction_id,
															sv.payment_status,
															sv.reg_fee,
															sv.amount_paid,
															sv.sv_event_id_fk
												FROM      	sv_reg_history sv
												WHERE       sv.sv_reg_history_id = ' . $this->db->escape( $svRegHistoryId ) . '
												AND			sv.year = ' . $this->db->escape( $curyear ) . '
												AND			sv.enabled = 1
											  ');

			
			$sv_registration_value = new Swamivisits_registration_value();
			if( $eventlist->num_rows() > 0)
			{
				foreach ($eventlist->result() as $regIdRow)
				{		
					$sv_registration_value->setSVRegHistoryID($regIdRow->sv_reg_history_id);
					$sv_registration_value->setEventID($regIdRow->sv_event_id_fk);
					$sv_registration_value->setCurrentYear($curyear);
					$sv_registration_value->setFirstName($regIdRow->first_name);
					$sv_registration_value->setLastName($regIdRow->last_name);
					$sv_registration_value->setEmail($regIdRow->email);
					$sv_registration_value->setNumRegistered($regIdRow->numreg);
					$sv_registration_value->setRegistrationFee( $regIdRow->reg_fee );
					$sv_registration_value->setPaymentType( $regIdRow->payment_type );
					$sv_registration_value->setCheckNum( $regIdRow->check_num );
					$sv_registration_value->setPaymentTransactionID( $regIdRow->payment_transaction_id );
					$sv_registration_value->setPaymentStatus( $regIdRow->payment_status );
				}
			}
				
			return $sv_registration_value;
		}

		//Confirms registration after payment is confirmed through Paypal ipn
		public function sendEventRegistrationConfirmEmailPaypal( $sv_registration_value, $desc, $curyear, $paypal_txn_id )
		{
			$this->load->library('encrypt');

			$sv_registration_value = Swamivisits_registration_value::cast($sv_registration_value);
			
			$message = '';
			$message .= '<p><b>You have Been Registered for ' . $curyear . ' ' . $desc.'</b></p>';
			$message .= '<p>Hari OM ' . $sv_registration_value->getFirstName() . 'ji,</p>';
			$message .= '<p>Your registration for ' . $curyear . ' ' . $desc . ' has been confirmed:</p>';
			$message .= '<p>';
			$message .= '<table border="0">';
			$message .= '<tr><td width="250">Name:</td><td>' . $sv_registration_value->getFirstName() . ' ' . $sv_registration_value->getLastName() . '</td></tr>';
			$message .= '<tr><td width="250">Email:</td><td>' . $sv_registration_value->getEmail() . '</td></tr>';
			$message .= '<tr><td width="250">Number of participants:</td><td>' . $sv_registration_value->getNumRegistered() . '</td></tr>';
			$message .= '<tr><td width="250">Total:</td><td>$' . $sv_registration_value->getTotalRegistrationFee() . '</td></tr>';
			$message .= '<tr><td width="250">Paypal Transaction ID:</td><td>' . $paypal_txn_id . '</td></tr>';
			$message .= '<tr><td width="250">Event:</td><td>' . $desc . '</td></tr>';
			$message .= '</table>';
			$message .= '</p>';
			$message .= '<p>Sincerely,</p>';
			$message .= '<p>Registration Team</p>';
			$message .= '<p>Chinmaya Mission Boston<br /></p>';
			
			$this->load->library('email');
			$this->email->set_newline("\r\n");
			
			$config['protocol'] = $this->config->item('email_smtp_protocol');
			$config['smtp_host'] = $this->config->item('email_smtp_host');
			$config['smtp_user'] = $this->config->item('email_smtp_user');
			$config['smtp_pass'] = $this->config->item('email_smtp_pass');
			$config['smtp_port'] = $this->config->item('email_smtp_port');
			$config['mailtype'] = $this->config->item('email_smtp_mailtype');
	
			$this->email->initialize($config);
	
			$this->email->from($this->config->item('email_smtp_user'), $this->config->item('email_smtp_user_name'));
			$this->email->to( $sv_registration_value->getEmail() );
			$this->email->cc('');
			$this->email->bcc('cmbproject@gmail.com');
			
			$this->email->subject('Registration confirmation for ' . $curyear .' '. $desc);
			$this->email->message($message);
			
			$this->email->send();
		}

		//Email with information about sending check after person has chosen to pay by check
		public function sendEventRegistrationByCheckEmail( $sv_registration_value, $desc, $curyear )
		{
			$sv_registration_value = Swamivisits_registration_value::cast($sv_registration_value);
			
			$message = '';
			$message .= '<p>Hari OM ' . $sv_registration_value->getFirstName() . 'ji,</p>';
			$message .= '<p>We received your registration request with payment by <u>check</u> for ' . $curyear . ' '. $desc .':</p>';
			$message .= '<p>';
			$message .= '<table border="0">';
			$message .= '<tr><td width="250">Name:</td><td>' . $sv_registration_value->getFirstName() . ' ' . $sv_registration_value->getLastName() . '</td></tr>';
			$message .= '<tr><td width="250">Email:</td><td>' . $sv_registration_value->getEmail() . '</td></tr>';
			$message .= '<tr><td width="250">Number of participants:</td><td>' . $sv_registration_value->getNumRegistered() . '</td></tr>';
			$message .= '<tr><td width="250">Total:</td><td>$' . $sv_registration_value->getTotalRegistrationFee() . '</td></tr>';
			$message .= '<tr><td width="250">Event:</td><td>' . $desc . '</td></tr>';
			$message .= '</table>';
			$message .= '</p>';
			$message .= '<p>Please write a check for <b>$' . $sv_registration_value->getTotalRegistrationFee() . '</b>, payable to <u>Chinmaya Mission Boston</u>. Mail the check to:</p>';
			$message .= '<table border="0">';
			$message .= '<tr><td width="250"></td><td>ATTN: '.$desc.' Registration</td></tr>';
			$message .= '<tr><td width="250"></td><td>Chinmaya Mission Boston</td></tr>';
			$message .= '<tr><td width="250"></td><td>1 Union Street</td></tr>';
			$message .= '<tr><td width="250"></td><td>Andover, MA  01810</td></tr>';
			$message .= '</table>';
			$message .= '</p>';
			$message .= '<p>You will receive a confirmation email once we receive your check. We would like to thank you for your support.</p>';
			$message .= '<p>Sincerely,</p>';
			$message .= '<p>Registration Team</p>';
			$message .= '<p>Chinmaya Mission Boston<br /></p>';
			
			$this->load->library('email');
			$this->email->set_newline("\r\n");
			
			$config['protocol'] = $this->config->item('email_smtp_protocol');
			$config['smtp_host'] = $this->config->item('email_smtp_host');
			$config['smtp_user'] = $this->config->item('email_smtp_user');
			$config['smtp_pass'] = $this->config->item('email_smtp_pass');
			$config['smtp_port'] = $this->config->item('email_smtp_port');
			$config['mailtype'] = $this->config->item('email_smtp_mailtype');
	
			$this->email->initialize($config);
	
			$this->email->from($this->config->item('email_smtp_user'), $this->config->item('email_smtp_user_name'));
			$this->email->to( $sv_registration_value->getEmail() );
			$this->email->cc('cmbproject@gmail.com');
			
			$this->email->subject('Payment by check for ' . $curyear . ' '. $desc);
			$this->email->message($message);
			
			$this->email->send();
		}	

		//Confirms registration after payment via check is released
		public function sendEventRegistrationCheckConfirmEmail( $sv_registration_value, $curyear)
		{
			$this->load->library('encrypt');

			$sv_registration_value = Swamivisits_registration_value::cast($sv_registration_value);
			
			$desc = $this->getEventName( $sv_registration_value->getEventID() );
			
			$message = '';
			$message .= '<p><b>You have Been Registered for ' . $curyear . ' ' . $desc.'</b></p>';
			$message .= '<p>Hari OM ' . $sv_registration_value->getFirstName() . 'ji,</p>';
			$message .= '<p>Your registration for ' . $curyear . ' ' . $desc . ' has been confirmed:</p>';
			$message .= '<p>';
			$message .= '<table border="0">';
			$message .= '<tr><td width="250">Name:</td><td>' . $sv_registration_value->getFirstName() . ' ' . $sv_registration_value->getLastName() . '</td></tr>';
			$message .= '<tr><td width="250">Email:</td><td>' . $sv_registration_value->getEmail() . '</td></tr>';
			$message .= '<tr><td width="250">Number of participants:</td><td>' . $sv_registration_value->getNumRegistered() . '</td></tr>';
			$message .= '<tr><td width="250">Total:</td><td>$' . $sv_registration_value->getTotalRegistrationFee() . '</td></tr>';
			$message .= '<tr><td width="250">Check#:</td><td>' . $sv_registration_value->getCheckNum() . '</td></tr>';
			$message .= '<tr><td width="250">Event:</td><td>' . $desc . '</td></tr>';
			$message .= '</table>';
			$message .= '</p>';
			$message .= '<p>Sincerely,</p>';
			$message .= '<p>Registration Team</p>';
			$message .= '<p>Chinmaya Mission Boston<br /></p>';
			
			$this->load->library('email');
			$this->email->set_newline("\r\n");
			
			$config['protocol'] = $this->config->item('email_smtp_protocol');
			$config['smtp_host'] = $this->config->item('email_smtp_host');
			$config['smtp_user'] = $this->config->item('email_smtp_user');
			$config['smtp_pass'] = $this->config->item('email_smtp_pass');
			$config['smtp_port'] = $this->config->item('email_smtp_port');
			$config['mailtype'] = $this->config->item('email_smtp_mailtype');
	
			$this->email->initialize($config);
	
			$this->email->from($this->config->item('email_smtp_user'), $this->config->item('email_smtp_user_name'));
			$this->email->to( $sv_registration_value->getEmail() );
			$this->email->cc('');
			$this->email->bcc('cmbproject@gmail.com');
			
			$this->email->subject('Registration confirmation for ' . $curyear .' '. $desc);
			$this->email->message($message);
			
			$this->email->send();
		}
	}
?>